package com.ctshk.rpc.bus.mapper;

import java.time.LocalDate;
import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.common.service.mapper.CtsBaseMapper;
import com.ctshk.common.service.mapper.CtsSQL;
import com.ctshk.rpc.bus.dto.BusProductManagePageDTO;
import com.ctshk.rpc.bus.dto.BusProductStrategyByChannelDTO;
import com.ctshk.rpc.bus.dto.BusProductStrategyDTO;
import com.ctshk.rpc.bus.entity.BusProductStrategy;
import com.ctshk.rpc.bus.req.BusProductPageReq;

/**
 * <p>
 * 巴士产品加幅策略 Mapper 接口
 * </p>
 *
 * @author 彭柳霖
 * @since 2021-02-19
 */
public interface BusProductStrategyMapper extends CtsBaseMapper<BusProductStrategy> {


    /**
     * 产品中心-获取巴士票产品分页列表
     * @param req
     * @return
     */
    @Select("<script>"+"SELECT b.id,b.departure_city_name,b.arrive_city_name,\n" +
            "b.shift_code,b.departure_time,b.boarding_station_name, \n" +
            "l.gateway_name,b.alighting_station_name,b.bus_companies_name,b.product_source, \n" +
            "b.supplier_name,b.intended_for,b.pack_status,b.on_shelf_status,b.recommendation_status \n" +
            "from t_bus_product b \n" +
            "left join t_bus_product_strategy a on b.id  = a.bus_product_id \n" +
            "left join t_bus_line l on b.line_id = l.id \n" +
            "WHERE b.is_deleted = 0 and b.sale_status = 1 <if test=\"req.busCompaniesName != null and req.busCompaniesName != ''\">AND b.bus_companies_name LIKE CONCAT('%',#{req.busCompaniesName},'%')</if> \n" +
            "<if test=\"req.shiftCode != null and req.shiftCode != ''\">AND b.shift_code LIKE CONCAT('%',#{req.shiftCode},'%')</if> \n" +
            "<if test=\"req.departureCityName != null and req.departureCityName != ''\">AND b.departure_city_name LIKE CONCAT('%',#{req.departureCityName},'%')</if> \n" +
            "<if test=\"req.arriveCityName != null and req.arriveCityName != ''\">AND b.arrive_city_name LIKE CONCAT('%',#{req.arriveCityName},'%')</if> \n" +
            "<if test=\"req.boardingStationName != null and req.boardingStationName != ''\">AND b.boarding_station_name LIKE CONCAT('%',#{req.boardingStationName},'%')</if> \n" +
            "<if test=\"req.alightingStationName != null and req.alightingStationName != ''\">AND b.alighting_station_name LIKE CONCAT('%',#{req.alightingStationName},'%')</if> \n" +
            "<if test=\"req.productSource != null and req.productSource != 0\">AND b.product_source = #{req.productSource}</if> \n" +
            "<if test=\"req.recommendationStatus != null and req.recommendationStatus != 0\">AND b.recommendation_status = #{req.recommendationStatus}</if> \n" +
            "<if test=\"req.supplierName != null and req.supplierName != ''\">AND b.supplier_name LIKE CONCAT('%',#{req.supplierName},'%')</if> \n" +



            "<if test=\"req.channelType != null and req.channelType == 1\">" +
            "and a.id in  ( \n"+
            "select increase_strategy_id from t_bus_product_channel_online_self_platform   \n"+
            "union select increase_strategy_id from  t_bus_product_channel_online_third_platform"+
            ") \n"+
            "</if> \n" +

            "<if test=\"req.channelType != null and req.channelType == 2\">" +
            "and a.id in  ( \n"+
            "select increase_strategy_id from t_bus_product_channel_online_third_platform"+
            ") \n"+
            "</if> \n" +

            "<if test=\"req.channelType != null and req.channelType == 3\">" +
            "and a.id in  ( \n"+
            "select increase_strategy_id from t_bus_product_channel_online_self_platform"+
            ") \n"+
            "</if> \n" +

            "<if test=\"req.channelType != null and req.channelType == 4\">" +
            "and a.id in  ( \n"+
            "select increase_strategy_id from t_bus_product_channel_offline_self_custom   \n"+
            "union select increase_strategy_id from  t_bus_product_channel_offline_agent_custom"+
            ") \n"+
            "</if> \n" +

            "<if test=\"req.channelType != null and req.channelType == 5\">" +
            "and a.id in  ( \n"+
            "select increase_strategy_id from t_bus_product_channel_offline_self_custom"+
            ") \n"+
            "</if> \n" +

            "<if test=\"req.channelType != null and req.channelType == 6\">" +
            "and a.id in  ( \n"+
            "select increase_strategy_id from t_bus_product_channel_offline_agent_custom"+
            ") \n"+
            "</if> \n" +

            "GROUP BY b.id "+"</script>")
    IPage<BusProductManagePageDTO> queryList(@Param("req") BusProductPageReq req, IPage<BusProductManagePageDTO> agentPage);



    /**
     * 根据产品id和日期查询策略
     */
    @Select("<script>"+"select a.id,a.increase_strategy_name," +
            "a.self_sale_permission_type," +
            "a.agent_sale_permission_type,a.self_sale_permission_id," +
            "a.agent_sale_permission_id " +
            "from t_bus_product_strategy a \n" +
            "left join t_bus_product b on a.bus_product_id  = b.id \n" +
            "left join t_bus_product_strategy_date c on a.id = c.increase_strategy_id \n" +
            "WHERE b.is_deleted = 0 \n" +
            "AND c.date_begin &lt;=#{queryDate} and c.date_end &gt;=#{queryDate}\n" +
            "AND b.id =#{productId} \n" +
            "group by b.id limit 1\n" +
            "</script>")
    BusProductStrategyDTO queryBusProductStrategyInfo(@Param("productId") Long productId,@Param("queryDate") LocalDate queryDate);
    
    /**
     * 根据产品id和日期查询策略
     */
    @Select("<script>"+"select a.id,a.increase_strategy_name," +
            "a.self_sale_permission_type," +
            "a.agent_sale_permission_type,a.self_sale_permission_id," +
            "a.agent_sale_permission_id " +
            "from t_bus_product_strategy a \n" +
            "join t_bus_product b on a.bus_product_id  = b.id \n" +
            "join t_bus_product_strategy_date c on a.id = c.increase_strategy_id \n" +
            "WHERE b.is_deleted = 0 \n" +
            "AND c.date_begin &lt;=#{queryDate} and c.date_end &gt;=#{queryDate}\n" +
            "AND b.id =#{productId} \n" +
            "group by b.id limit 1\n" +
            "</script>")
    BusProductStrategy findBusProductStrategy(@Param("productId") Long productId,@Param("queryDate") LocalDate queryDate);


    @Select({"<script>",
            "select id from t_bus_product_strategy where 1=1 ",
            "and bus_product_id in <foreach collection='list' item='item' open='(' close=')' separator=','>",
            "#{item}",
            "</foreach>",
            "</script>"})
    List<Long> selectIdListByBusProductIds(List<Long> busProductIds);
    
    /**
     * 查询日期范围内的产品的加幅策略列表
     * @param productId
     * @param beginDate
     * @param endDate
     * @return
     */
    @Select("select t.* from t_bus_product_strategy t where t.is_deleted = 0 and t.bus_product_id = #{productId} "
    		+ " and exists (select 1 from t_bus_product_strategy_date td where td.increase_strategy_id = t.id and (td.date_begin >= #{beginDate} or td.date_end < #{beginDate}) limit 1) ")
    List<BusProductStrategy> queryListByValidDate(@Param("productId") Long productId, @Param("beginDate") LocalDate beginDate, @Param("endDate") LocalDate endDate);
    
    /**
     * 查询巴士产品渠道相关加幅信息
     * @return
     */
    @SelectProvider(type = BusProductStrategyMapper.class, method = "findStrategyListByChannelProvider")
    BusProductStrategyByChannelDTO findStrategyListByChannel(@Param("productId") Long productId, @Param("queryDate") LocalDate queryDate
    		, @Param("channelId") Long channelId);
    
    static String findStrategyListByChannelProvider(Long productId, LocalDate queryDate, Long channelId) {
    	return new CtsSQL() {{
    		SELECT("tisr.price_type, tisr.price");
    		FROM("t_bus_product_strategy t");
    		JOIN("t_bus_product_strategy_date tsd on tsd.increase_strategy_id = t.id"
    				, "t_bus_product_channel_online_self_platform tosp on tosp.increase_strategy_id = t.id"
    				, "t_bus_product_increase_strategy_rule tisr on tisr.increase_strategy_id = t.id");
    		WHERE("t.is_deleted = 0 and tosp.is_deleted = 0 and t.bus_product_id = #{productId} and tosp.channel_id = #{channelId}"
    				, "tsd.date_begin <= #{queryDate} and tsd.date_end >= #{queryDate}");
    		LIMIT(1);
    	}}.toString();
    }
}
